﻿Imports MySql.Data.MySqlClient
Imports System.IO
Imports DevExpress.XtraGrid.Views.Grid
Public Class frmOPProced
    Dim DA As New MySqlDataAdapter
    Dim MyConn As New MySqlConnection
    Dim mysql As New ClsSQLhelper
    Dim DSOPPP As New OPPP
    Dim Strsql As String
    Dim Curr_View As String = "Default"
    Dim On_load As Boolean = True
    Dim StrMainsql As String = "select m_proced.PID,m_person.CID,m_person.HN,Concat(m_person.NAME,'  ',m_person.LNAME)" & _
" as NAME, m_proced.SEQ,m_proced.DATE_SERV,m_proced.CLINIC,m_proced.PROCED,m_proced.SERVPRIC,m_proced.D_UPDATE,m_proced.CODE_ID " & _
" from m_proced left join m_person on  m_proced.PID = m_person.PID  "

#Region "Function"
    Sub Search_Information()
        Try
            If Me.VGR_PID.Properties.Value = "000000" Then
                MsgBox("ไม่สามารถหาความเชื่อมโยงได้เนื่องจาก PID = 000000")
                Exit Sub
            End If
            Select Case Me.VGR_CID.Properties.Value
                Case "0000000000000"
                    If MsgBox("รหัสบัตรไม่ถูกกต้อง คุณยืนยันที่จะค้นหาต่อหรือไม่", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo, "คำเตือน") = MsgBoxResult.No Then Exit Sub
                    frmSearchInfo.TxtCriteria.Text = Me.VGR_PID.Properties.Value
                Case ""
                    If Me.VGR_HN.Properties.Value <> "ไม่ระบุ" Then

                        frmSearchInfo.TxtCriteria.Text = Me.VGR_HN.Properties.Value
                    Else
                        If Me.VGR_PID.Properties.Value <> "" Then
                            frmSearchInfo.TxtCriteria.Text = Me.VGR_PID.Properties.Value
                        ElseIf Me.VGR_Fullname.Properties.Value <> "" Then
                            frmSearchInfo.TxtCriteria.Text = Me.VGR_Fullname.Properties.Value
                        Else

                            MsgBox("ไม่สามารถ หาความเชื่อมโยงได้", MsgBoxStyle.Critical)
                        End If

                    End If
                Case Else
                    frmSearchInfo.TxtCriteria.Text = Me.VGR_CID.Properties.Value
            End Select
            With frmSearchInfo
                .TxtYear.Text = Me.TxtYear.Text
                .TxtMonth.Text = Me.TxtMonth.Text
                '.TxtDateServ.Text = Me.TxtDateServ.Text
                .Submit_Search()
                .ShowDialog()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub GenScript()
        Try
            Dim Strsql As String = " CID in ("
            Dim HNSql As String = " HN in ("
            Dim PIDsql As String = " person_id in ("
            If Me.DataGridView1.RowCount = 0 Then Exit Sub

            Select Case Me.Curr_View

                Case "PX9200"

                    For i As Integer = 0 To DataGridView1.RowCount - 1

                        Strsql = Strsql & "'" & DataGridView1.Rows(i).Cells("CID").Value.ToString.Trim() & "',"
                        HNSql = HNSql & "'" & DataGridView1.Rows(i).Cells("HN").Value.ToString.Trim() & "',"
                        PIDsql = PIDsql & "'" & DataGridView1.Rows(i).Cells("PID").Value.ToString.Trim() & "',"
                    Next
                    Strsql = Mid(Strsql, 1, Len(Strsql) - 1) & ")"
                    HNSql = Mid(HNSql, 1, Len(HNSql) - 1) & ")"
                    PIDsql = Mid(PIDsql, 1, Len(PIDsql) - 1) & ")"
                    SQLDialog.Sqltext.Text = Strsql
                    SQLDialog.Sqltext2.Text = HNSql
                    SQLDialog.Sqltext3.Text = PIDsql

                    SQLDialog.ShowDialog()


                Case Else
                    Strsql = " CID in ("
                    For i As Integer = 0 To DataGridView1.RowCount - 1

                        Strsql = Strsql & "'" & DataGridView1.Rows(i).Cells("CID").Value.ToString.Trim() & "',"
                        HNSql = HNSql & "'" & DataGridView1.Rows(i).Cells("HN").Value.ToString.Trim() & "',"
                        PIDsql = PIDsql & "'" & DataGridView1.Rows(i).Cells("PID").Value.ToString.Trim() & "',"
                    Next
                    Strsql = Mid(Strsql, 1, Len(Strsql) - 1) & ")"
                    HNSql = Mid(HNSql, 1, Len(HNSql) - 1) & ")"
                    PIDsql = Mid(PIDsql, 1, Len(PIDsql) - 1) & ")"
                    SQLDialog.Sqltext.Text = Strsql
                    SQLDialog.Sqltext2.Text = HNSql
                    SQLDialog.Sqltext3.Text = PIDsql

                    SQLDialog.ShowDialog()

            End Select

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Load_Summary()
        Try
            Strsql = "select CODE_ID,Count(CODE_ID) as Totalrows from m_proced where filename = " & CboImportOPFile.SelectedValue & " and code_id is not null  group by code_id "
            Dim DT_Service As New DataTable
            DT_Service = mysql.GetMYSQLDataTable(Strsql, "ERROR_Summary")

            With Grid_ErrorCode
                .DataSource = DT_Service

            End With
            With DataGridView1

            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Load_Data(ByVal Code_ID As String, Optional ByVal StrAddonSql As String = "")
        Try

            Me.Cursor = Cursors.WaitCursor
            Me.Curr_View = Code_ID
            'DSOPPP.Tables("m_service").Clear()
            'Strsql = "Select m_person.PCUCODE,m_person.PID,m_person.HN,m_person.CID,Concat(m_person.NAME,'  ',m_person.LNAME) as NAME,m_person.SEX,m_person.BIRTH,m_person.MSTATUS,m_person.OCCUPA,m_person.NATION,m_person.TYPEAREA,m_person.CODE_ID from m_person where status = 1 and code_id Like '%OP9200%' and FILENAME = " & Me.CboImportOPFile.SelectedValue
            '  Strsql = "select m_service.PCUCODE,m_service.PID,m_person.CID,m_person.HN,Concat(m_person.NAME,'  ',m_person.LNAME) as NAME,m_person.SEX,m_person.BIRTH,m_service.SEQ,m_service.DATE_SERV,m_service.CLINIC,m_service.PRICE,m_service.PAY,m_service.SERVICE_TYPE,m_service.REFERIN,m_service.REFINHOS,m_service.REFEROUT,m_service.REFOUHOS  from m_service left join m_person on  m_service.PID = m_person.PID  where m_service.CODE_ID  like  '%OP9200%' and m_service.FILENAME = " & Me.CboImportOPFile.SelectedValue & "   order by  m_service.PID"
            Strsql = StrMainsql & "  where m_proced.CODE_ID  like  '%" & Code_ID & "%' " & StrAddonSql & "  and m_proced.FILENAME = " & Me.CboImportOPFile.SelectedValue
            If Me.ChkShowPIDError.Checked = False Then
                Strsql = Strsql & " and  m_proced.pid <> 000000 "
            End If
            If Me.ChkShowNewBorn.Checked = False Then
                Strsql = Strsql & " and  m_person.NAME not like '(%' "
            End If
            Strsql = Strsql & "   order by  m_proced.PID desc"

            '  DA = New MySqlDataAdapter(Strsql, MyConn)
            ' DA.Fill(DSOPPP, "m_person")
            Dim DT_Person As New DataTable
            DT_Person = mysql.GetMYSQLDataTable(Strsql, "m_proced")
            With DataGridView1
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_Person

                If Me.DataGridView1.RowCount < 1 Then
                    MsgBox("ไม่พบข้อมูล", MsgBoxStyle.Exclamation)
                Else
                    MsgBox("ดึงข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                End If
            End With
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.Message)
        End Try
    End Sub

#End Region



    Private Sub CboImportOPFile_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboImportOPFile.SelectedIndexChanged
        Try
            If On_load = True Then Exit Sub
            If InStr(Me.CboImportOPFile.Text, "FOP", CompareMethod.Text) > 0 Then
                Me.TxtYear.Text = Mid(Me.CboImportOPFile.Text, 11, 4)
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 15, 2)
            End If
            If InStr(Me.CboImportOPFile.Text, "E_PCU", CompareMethod.Text) > 0 Then
                'E_PCU_11266_201010_20101202.zip
                Me.TxtYear.Text = Val(Mid(Me.CboImportOPFile.Text, 13, 4)) + 543
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 17, 2)
            End If

            '  Read_DataQuality()
            Load_Summary()
            'Select Case Me.Curr_View
            '    Case "PX9200"
            '        BtnPX9200_Click(sender, e)
            '    Case "PX9299"
            '        BtnPX9299_Click(sender, e)
            '        'Case "DX9230"
            '        '   BtnDX9230_Click(sender, e)
            'End Select
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

   
    Private Sub frmOPProced_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            'Me.TxtZoomCID.Text = "0000000000000"
            Dim DT_m_files_upload As DataTable
            Strsql = "select ROWID,FILENAME from m_files_upload "
            Strsql = Strsql & " where TYPE_DESC = 'OP Package' or  TYPE_DESC = '18 แฟ้ม' "
            Strsql = Strsql & " order by Date_SEND DESC"


            With Me.CboImportOPFile

                DT_m_files_upload = mysql.GetMYSQLDataTable(Strsql, "m_files_upload")
                If DT_m_files_upload.Rows.Count = 0 Then
                    MsgBox("คุณต้องนำข้อมูลเข้าโปรแกรมตรวจสอบ NHSO OPPP 2554 ก่อน", MsgBoxStyle.Exclamation)
                    Exit Sub
                End If
                .DataSource = DT_m_files_upload
                .DisplayMember = "FILENAME"
                .ValueMember = "ROWID"
                Me.CboImportOPFile.SelectionStart = DT_m_files_upload.Rows(0).Item("ROWID").ToString

            End With
            If InStr(Me.CboImportOPFile.Text, "FOP", CompareMethod.Text) > 0 Then
                Me.TxtYear.Text = Mid(Me.CboImportOPFile.Text, 11, 4)
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 15, 2)
            End If
            If InStr(Me.CboImportOPFile.Text, "E_PCU", CompareMethod.Text) > 0 Then
                'E_PCU_11266_201010_20101202.zip
                Me.TxtYear.Text = Val(Mid(Me.CboImportOPFile.Text, 13, 4)) + 543
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 17, 2)
            End If
            Load_Summary()

            Strsql = "Select (((AMOUNT-ERROR)/amount))*100 as point from m_upload where filename = 'PROCED'"
            Dim MyPoint As String
            Dim DT_Point As New DataTable
            DT_Point = mysql.GetMYSQLDataTable(Strsql, "point")

            If DT_Point.Rows.Count <> 0 Then
                MyPoint = Val(DT_Point.Rows(0).Item(0).ToString)
            Else
                MyPoint = 0
            End If




            Me.CircularGauge1.Scales(0).Value = Val(MyPoint)
            Me.CircularGauge1.Labels(0).Text = IIf(MyPoint = 100, "100 %", FormatNumber(MyPoint, 2) & " %")
            On_load = False


            On_load = False

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        MyConn.ConnectionString = ClsSQLhelper.MySQLConnectionStr
    End Sub
  

   




    'Private Sub TxtProced_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '    Try
    '        If Me.TxtProced.Text <> "" Then
    '            Strsql = "Select DESC_R from l_icd9 where CODE =  '" & Me.TxtProced.Text.Trim & "'"
    '            Me.TSProcedDesc.Text = "PROCED " & Me.TxtProced.Text & " : " & mysql.MySQLExecuteScalar(Strsql)
    '            If Me.TSProcedDesc.Text = "PROCED " & Me.TxtProced.Text & " : " Then
    '                Me.TSProcedDesc.Text = "PROCED  " & Me.TxtProced.Text & " : ไม่พบข้อมูล"
    '            End If
    '        End If

    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '    End Try

    'End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try
            Dim Error_Code As String

            With Me.DataGridView1
                Dim MyPerson As New ClsPerson
                MyPerson.Get_NHSO_Profile(.Rows(e.RowIndex).Cells("PID").Value.ToString.Trim)

                Me.VGR_CID.Properties.Value = MyPerson.CID
                Me.VGR_PID.Properties.Value = .Rows(e.RowIndex).Cells("PID").Value.ToString.Trim
                Me.VGR_Fullname.Properties.Value = MyPerson.Get_Person_FullName
                Me.VGR_HN.Properties.Value = MyPerson.HN
                Me.VGR_MStatus.Properties.Value = MyPerson.MStatus
                Me.VGR_FormatCID.Properties.Value = MyPerson.Get_Person_FormatCID
                Me.VGR_Nationality.Properties.Value = MyPerson.Nation
                Me.VGR_Occupation.Properties.Value = MyPerson.Ocupaton
                Me.VGR_Race.Properties.Value = MyPerson.Race
                Me.VGR_Sex.Properties.Value = MyPerson.Sex
                Me.VGR_TypeArea.Properties.Value = MyPerson.TypeArea
                Me.VGA_BirthDay.Properties.Value = MyPerson.BirthDay
                Me.VGA_AGE.Properties.Value = MyPerson.Age
                Error_Code = DataGridView1.Rows(e.RowIndex).Cells("CODE_ID").Value

                If Mid(Error_Code, 1, 1) = "," Then
                    Error_Code = Mid(Error_Code, 2, Error_Code.Length)
                End If
                If InStr(Error_Code, ",", CompareMethod.Text) > 0 Then
                    Error_Code = Replace(Error_Code, ",", "','")
                End If
                Strsql = "select code_id,remark from l_error_code where code_id in ('" & Error_Code & "')"
                Dim DT_ERROR As New DataTable
                DT_ERROR = mysql.GetMYSQLDataTable(Strsql, "l_error_code")
                With ErrorDesc_Grid
                    .DataSource = DT_ERROR
                End With



                Strsql = "select name from icd9cm1 where code='" & DataGridView1.Rows(e.RowIndex).Cells("PROCED").Value.ToString & "'"
                VGR_HOSxP_PROCED.Properties.Value = mysql.MySQLExecuteScalar(Strsql, "hos")

                Strsql = "Select DESC_R from l_icd9 where code='" & DataGridView1.Rows(e.RowIndex).Cells("PROCED").Value.ToString & "'"
                VGR_NHSO_PROCED.Properties.Value = mysql.MySQLExecuteScalar(Strsql)
            End With
        Catch ex As Exception

        End Try
    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        Search_Information()
    End Sub

    Private Sub DataGridView1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown
        If e.KeyCode = Keys.F5 Then
            Search_Information()
        End If
    End Sub

    Private Sub RBM_GenScript_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_GenScript.ItemClick
        GenScript()
    End Sub

  
    Private Sub RBM_Find_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_Find.ItemClick
        Search_Information()
    End Sub

    Private Sub RBM_Excel_Export_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_Excel_Export.ItemClick
        If Me.DataGridView1.RowCount = 0 Then Exit Sub


        If Me.DataGridView1.RowCount = 0 Then Exit Sub
        If MsgBox("คุณต้องการส่งออกข้อมูลในตาราง ออกเป็นรูปแบบ Excel หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Export to Excel") = MsgBoxResult.No Then Exit Sub


        With SaveFileDialog1
            .FileName = Me.Text
            .Filter = "Excel 2003 | *.xls"
            If .ShowDialog() <> DialogResult.OK Then Exit Sub

            Dim Str As String = "" ' = "PCUCODE,PID,HN,CID,NAME,SEX,BIRTH,MSTATUS,OCCUPA,NATION,TYPEAREA,CODE_ID"
            For i = 0 To DataGridView1.ColumnCount - 1
                Str = Str & DataGridView1.Columns(i).HeaderText
                If i <> DataGridView1.ColumnCount - 1 Then
                    Str = Str & ","
                End If

            Next
            Using sw As StreamWriter = New StreamWriter(.FileName, False, System.Text.Encoding.UTF8)
                sw.WriteLine(Str)

                '  
                For i As Integer = 0 To DataGridView1.RowCount - 1
                    Str = ""
                    For j As Integer = 0 To DataGridView1.ColumnCount - 1
                        Str = Str & DataGridView1.Rows(i).Cells(j).Value.ToString
                        If j <> DataGridView1.ColumnCount - 1 Then
                            Str = Str & ","
                        End If

                    Next
                    sw.WriteLine(Str)
                Next
                sw.Close()

            End Using


            MsgBox("ส่งออกข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)

        End With
    End Sub

   
    Private Sub RBM_PX1104_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_PX1104.ItemClick
        Load_Data("PX1104")
    End Sub

    Private Sub RBM_PX1105_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_PX1105.ItemClick
        Load_Data("PX1105")
    End Sub

    Private Sub RBM_PX1106_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_PX1106.ItemClick
        Load_Data("PX1106")
    End Sub

    Private Sub RBM_PX1130_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_PX1130.ItemClick
        Load_Data("PX1130")
    End Sub

    Private Sub RBM_PX9200_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_PX9200.ItemClick
        Load_Data("PX9200")
    End Sub

    Private Sub RBM_PX9230_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_PX9230.ItemClick
        Load_Data("PX9230")
    End Sub

    Private Sub RBM_PX9299_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_PX9299.ItemClick
        Load_Data("PX9299")
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub RBM_ICD9_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_ICD9.ItemClick
        frmICD9.ShowDialog()
    End Sub

    Private Sub GridView2_CustomRowCellEditForEditing(ByVal sender As System.Object, ByVal e As DevExpress.XtraGrid.Views.Grid.CustomRowCellEditEventArgs) Handles GridView2.CustomRowCellEditForEditing
        Try
            'If e.Column.FieldName = "Totalrows" Then Return
            Dim Gv As GridView = sender
            Dim CodeName As String = Gv.GetRowCellValue(e.RowHandle, _
              Gv.Columns("CODE_ID")).ToString()


            Load_Data(CodeName)
        Catch ex As Exception

        End Try
    End Sub

    Private Sub Grid_ErrorCode_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Grid_ErrorCode.Click

    End Sub
End Class